<?php
class Laporan_model extends CI_Model{
    public static $RET_MENU,$CHILD,$PARENT;
	
	function   __construct() {
        parent::__construct();

       }
	private $tbl = 'nota';
	function get_CetakPenjualanHarian($cabang, $tgl)
	{
			$query = $this->db->query("select distinct(a.intno_nota), upper(b.strnama_dealer) strnama_dealer, b.strnama_upline, upper(d.strnama_unit) strnama_unit, 
			upper(c.strnama_cabang) strnama_cabang, a.inttotal_omset, (select intcash from nota 
			where nota.datetgl = '$tgl'
			and nota.intid_cabang = $cabang
			and nota.intdp = 0 and nota.intid_nota = a.intid_nota) totalcash, a.intcash, a.intkkredit, a.intdebit, 
			a.intdp, a.inttotal_bayar, 
			(CASE WHEN f.intid_jbarang = 4 THEN a.inttotal_bayar ELSE 0 END) AS sk,
			(CASE WHEN f.intid_jbarang = 5 THEN a.inttotal_bayar ELSE 0 END) AS lg,
			(CASE WHEN f.intid_jbarang = 6 THEN a.inttotal_bayar ELSE 0 END) AS ll, 
			a.datetgl tgl, a.is_dp, jenis_penjualan.strnama_jpenjualan
			from nota a inner join cabang c on c.intid_cabang = a.intid_cabang
			inner join member b on b.intid_dealer = a.intid_dealer
			inner join unit d on d.intid_unit = b.intid_unit
			left outer join jenis_penjualan on a.intid_jpenjualan = jenis_penjualan.intid_jpenjualan, 
			nota_detail e, barang f, jenis_barang g
			where a.intid_nota = e.intid_nota
			and e.intid_barang = f.intid_barang
			and f.intid_jbarang = g.intid_jbarang
			and a.datetgl = '$tgl'
			and a.intid_cabang = $cabang
			and e.is_free = 0
			order by a.intno_nota asc");
        return $query->result();
	}
	
	function selectWeek () 
	{
		$query = $this->db->query("select * from week");
	    return $query->result();
	}
	
	function selectJPenjualan(){
       
	   $query = $this->db->query("select intid_jpenjualan, upper(strnama_jpenjualan) strnama_jpenjualan from jenis_penjualan order by intid_jpenjualan asc");
	   return $query->result();
	   }
 
	function get_CetakPenjualanMingguan($week, $cabang, $jpenjualan)
	{
		$query = $this->db->query("select x.strnama_dealer, x.strnama_upline, upper(x.strnama_cabang) strnama_cabang, x.strnama_unit, x.dateend, x.datestart, x.intid_week, x.intkomisi10, x.intkomisi20, x.intpv, x.inttotal_bayar,
		x.inttotal_omset, upper(x.strnama_jpenjualan) strnama_jpenjualan, x.intid_jpenjualan, y.omsett, y.omsetm, y.omsettc, y.omsetlg, y.omsetll
		from (select b.intid_dealer,a.intno_nota, (select sum(inttotal_omset) 
		from nota 
		where intid_week = $week
		and intid_cabang = $cabang
		and intid_jpenjualan = $jpenjualan
		and is_arisan = 0
		and is_dp = 0 and intid_dealer = a.intid_dealer) inttotal_omset, b.strnama_dealer, b.strnama_upline,  (select sum(intkomisi10) 
		from nota 
		where intid_week = $week
		and intid_cabang = $cabang
		and intid_jpenjualan = $jpenjualan
		and is_arisan = 0
		and is_dp = 0 and intid_dealer = a.intid_dealer) intkomisi10, (select sum(intkomisi20) 
		from nota 
		where intid_week = $week
		and intid_cabang = $cabang
		and intid_jpenjualan = $jpenjualan
		and is_arisan = 0
		and is_dp = 0 and intid_dealer = a.intid_dealer) intkomisi20, 
		(select sum(intpv) 
		from nota 
		where intid_week = $week
		and intid_cabang = $cabang
		and intid_jpenjualan = $jpenjualan
		and is_arisan = 0
		and is_dp = 0 and intid_dealer = a.intid_dealer) intpv, (select sum(inttotal_bayar) 
		from nota 
		where intid_week = $week
		and intid_cabang = $cabang
		and intid_jpenjualan = $jpenjualan
		and is_arisan = 0
		and is_dp = 0 and intid_dealer = a.intid_dealer) inttotal_bayar, a.intid_week,e.strnama_jpenjualan, e.intid_jpenjualan, 
		(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart,
		(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend,
		c.strnama_unit, d.strnama_cabang  
		from nota a, member b, unit c, cabang d, jenis_penjualan e
		where a.intid_dealer = b.intid_dealer 
		and a.intid_cabang = d.intid_cabang
		and b.intid_unit = c.intid_unit
		and a.intid_jpenjualan = e.intid_jpenjualan
		and a.is_dp = 0
		and a.intid_week = $week
		and a.intid_cabang = $cabang
		and a.intid_jpenjualan = $jpenjualan
		group by b.intid_dealer
		order by a.intno_nota asc ) x, (select a.intid_dealer, 
(select if((SELECT co FROM (SELECT intid_dealer,COUNT(*) AS co FROM (SELECT DISTINCT intid_dealer, intid_jbarang FROM nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week = $week
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and nota.is_arisan = 0
and nota.is_dp = 0) AS z GROUP BY intid_dealer) AS xx WHERE xx.intid_dealer = a.intid_dealer) = 2 ,sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga))),sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
from nota
where intid_week = $week
and intid_cabang = $cabang
and intid_jpenjualan = $jpenjualan
and is_arisan = 0 and nota.intid_dealer = a.intid_dealer)) 
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week = $week
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and barang.intid_jbarang=1
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_dealer = a.intid_dealer) as omsett,
(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
from nota
where intid_week = $week
and intid_cabang = $cabang
and intid_jpenjualan = $jpenjualan
and is_arisan = 0
and intid_dealer = a.intid_dealer) 
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week = $week
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and barang.intid_jbarang=2
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_dealer = a.intid_dealer) as omsetm,
(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
from nota
where intid_week = $week
and intid_cabang = $cabang
and intid_jpenjualan = $jpenjualan
and is_arisan = 0
and intid_dealer = a.intid_dealer) 
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week = $week
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and barang.intid_jbarang=3
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_dealer = a.intid_dealer) as omsettc,
(select sum(nota_detail.intquantity*nota_detail.intharga)
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week = $week
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and nota.is_arisan = 0
and nota.is_dp = 0
and barang.intid_jbarang=5
and nota.intid_dealer = a.intid_dealer) as omsetlg,
(select sum(nota_detail.intquantity*nota_detail.intharga)
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week = $week
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and barang.intid_jbarang=6
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_dealer = a.intid_dealer) as omsetll
from nota a, nota_detail b, barang c
where a.intid_nota = b.intid_nota
and b.intid_barang = c.intid_barang
and a.intid_week = $week
and a.intid_cabang = $cabang
and a.intid_jpenjualan = $jpenjualan
and b.is_free = 0
and a.is_arisan = 0
and a.is_dp = 0
group by a.intid_dealer)y
where x.intid_dealer = y.intid_dealer");
        return $query->result();
	}
	function get_CetakPenjualanSKMingguan($week, $cabang)
	{
		$query = $this->db->query("select a.datetgl, a.intno_nota, upper(b.strnama_dealer) strnama_dealer, upper(b.strnama_upline) strnama_upline, 
		upper(d.strnama_unit)strnama_unit,upper(c.strnama_cabang) strnama_cabang, a.intid_week,  
		(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart,
		(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend, 
		f.strnama, e.intquantity, h.intharga_jawa, h.intharga_luarjawa, c.intid_wilayah, sum(a.inttotal_bayar)inttotal_bayar 
		from nota a inner join cabang c on c.intid_cabang = a.intid_cabang
		inner join member b on b.intid_dealer = a.intid_dealer
		inner join unit d on d.intid_unit = b.intid_unit,
		nota_detail e, barang f, jenis_barang g, harga h
		where a.intid_nota = e.intid_nota
		and e.intid_barang = f.intid_barang
		and f.intid_jbarang = g.intid_jbarang
		and f.intid_barang = h.intid_barang
		and a.intid_week = $week
		and a.intid_cabang = $cabang
		and a.intid_jpenjualan = 10
		and e.is_free = 0
		group by a.datetgl, a.intno_nota, b.strnama_dealer, b.strnama_upline, d.strnama_unit,
		d.strnama_unit, a.intid_week, f.strnama, e.intquantity, h.intharga_jawa, h.intharga_luarjawa, c.intid_wilayah
		order by d.strnama_unit asc, b.strnama_upline asc");
        return $query->result();
	}
	
	function get_CetakPenjualanArisanMingguan($week, $cabang)
	{
		
		$query = $this->db->query("select a.datetgl, a.intno_nota, upper(b.strnama_dealer) strnama_dealer, upper(b.strnama_upline) strnama_upline, 
		upper(d.strnama_unit)strnama_unit,upper(c.strnama_cabang) strnama_cabang, a.intid_week,  
		(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart,
		(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend, 
		c.intid_wilayah, h.intcicilan_jawa, h.intcicilan_luarjawa, 
		h.intum_jawa, h.intum_luarjawa, 
		(select sum(inttotal_omset) from nota where nota.intid_week = $week and nota.intid_cabang = $cabang and nota.intid_jpenjualan = 1 and nota.is_arisan = 1 and nota.intid_nota = a.intid_nota) as inttotal_omset, 
		a.intid_jpenjualan, (select sum(intpv) from nota where nota.intid_week = $week and nota.intid_cabang = $cabang and nota.intid_jpenjualan = 1 and nota.is_arisan = 1 and nota.intid_nota = a.intid_nota)as intpv, 
		sum(e.intquantity)intquantity, (select sum(inttotal_bayar) from nota where nota.intid_week = $week and nota.intid_cabang = $cabang and nota.intid_jpenjualan = 1 and nota.is_arisan = 1 and nota.intid_nota = a.intid_nota) inttotal_bayar,	
		(h.intcicilan_jawa*(sum(e.intquantity))*1) as cicilan, 
		(select sum(intkomisi10) from nota where nota.intid_week = $week and nota.intid_cabang = $cabang and nota.intid_jpenjualan = 1 and nota.is_arisan = 1 and nota.intid_nota = a.intid_nota) as intkomisi10, 
		(select sum(intkomisi20) from nota where nota.intid_week = $week and nota.intid_cabang = $cabang and nota.intid_jpenjualan = 1 and nota.is_arisan = 1 and nota.intid_nota = a.intid_nota) as intkomisi20,
		(select sum(nota_detail.intharga*nota_detail.intquantity) from nota_detail where nota_detail.intid_nota = a.intid_nota) retail
		from nota a, member b, cabang c, unit d, nota_detail e, 
		barang f, harga h
		where a.intid_jpenjualan = 1
		and a.is_arisan = 1
		and e.is_free = 0
		and a.intid_week = $week
		and a.intid_dealer = b.intid_dealer
		and a.intid_cabang = $cabang
		and a.intid_nota = e.intid_nota
		and a.intid_cabang = c.intid_cabang
		and a.intid_unit = d.intid_unit
		and e.intid_barang = f.intid_barang
		and f.intid_barang = h.intid_barang
		group by a.intid_nota
		order by d.strnama_unit asc, b.strnama_upline asc");
		return $query->result();

			
	}
	
	function selectJPenjualanById($id){
       
	   $query = $this->db->query("select intid_jpenjualan, upper(strnama_jpenjualan) strnama_jpenjualan from jenis_penjualan where intid_jpenjualan = $id");
	   return $query->result();
    }
	
	function get_CetakPenjualanBulanan($month, $cabang, $jpenjualan)
	{/*
(select a.intid_dealer, 
(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if

(inttotal_omset =0, 0, intvoucher))
from nota
where intid_cabang = $cabang
and intid_jpenjualan = $jpenjualan
and is_arisan = 0
and intid_dealer = a.intid_dealer) 
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week in (select intid_week from week where intbulan = '$month')
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and barang.intid_jbarang=1
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_dealer = a.intid_dealer) 
*/
		$query = $this->db->query("select x.strnama_dealer, x.strnama_upline, upper(x.strnama_cabang) 

strnama_cabang, x.strnama_unit, x.intkomisi10, x.intkomisi20, x.intpv, x.inttotal_bayar,
		x.inttotal_omset, upper(x.strnama_jpenjualan) strnama_jpenjualan, x.intid_jpenjualan, y.omsett, 

y.omsetm, y.omsettc, y.omsetlg, y.omsetll
		from (select b.intid_dealer,a.intno_nota, sum(a.inttotal_omset) inttotal_omset, b.strnama_dealer, 

b.strnama_upline,  sum(a.intkomisi10) intkomisi10, sum(a.intkomisi20) intkomisi20, 
		sum(a.intpv) intpv, sum(a.inttotal_bayar) inttotal_bayar, a.intid_week,e.strnama_jpenjualan, 

a.intid_jpenjualan,
		c.strnama_unit, d.strnama_cabang  
		from nota a, member b, unit c, cabang d, jenis_penjualan e
		where a.intid_dealer = b.intid_dealer 
		and a.intid_cabang = d.intid_cabang
		and b.intid_unit = c.intid_unit
		and a.intid_jpenjualan = e.intid_jpenjualan
		and a.is_dp = 0
		and a.is_arisan != 1
		and a.intid_week in (select intid_week from week where intbulan = '$month')
		and a.intid_cabang = $cabang
		and a.intid_jpenjualan = $jpenjualan
		group by b.intid_dealer
		order by a.intno_nota asc ) x, 
(select a.intid_dealer, (select if((SELECT co FROM (SELECT intid_dealer,COUNT(*) AS co FROM (SELECT DISTINCT intid_dealer, intid_jbarang FROM nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week in (select intid_week from week where intbulan = '$month')
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and nota.is_arisan = 0
and nota.is_dp = 0) AS z GROUP BY intid_dealer) AS xx WHERE xx.intid_dealer = a.intid_dealer) = 2 ,sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga))),sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
from nota
where intid_week in (select intid_week from week where intbulan = '$month')
and intid_cabang = $cabang
and intid_jpenjualan = $jpenjualan
and is_arisan = 0 and nota.intid_dealer = a.intid_dealer)) 
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week in (select intid_week from week where intbulan = '$month')
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and barang.intid_jbarang=1
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_dealer = a.intid_dealer) as omsett,
(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if

(inttotal_omset =0, 0, intvoucher))
from nota
where intid_cabang = $cabang
and intid_jpenjualan = $jpenjualan
and is_arisan = 0
and intid_dealer = a.intid_dealer) 
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week in (select intid_week from week where intbulan = '$month')
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and nota.is_arisan = 0
and nota.is_dp = 0
and barang.intid_jbarang=2
and nota.intid_dealer = a.intid_dealer) as omsetm,
(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if

(inttotal_omset =0, 0, intvoucher))
from nota
where intid_cabang = $cabang
and intid_jpenjualan = $jpenjualan
and is_arisan = 0
and intid_dealer = a.intid_dealer) 
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week in (select intid_week from week where intbulan = '$month')
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and barang.intid_jbarang=3
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_dealer = a.intid_dealer) as omsettc,
(select sum(nota_detail.intquantity*nota_detail.intharga)
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week in (select intid_week from week where intbulan = '$month')
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and barang.intid_jbarang=5
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_dealer = a.intid_dealer) as omsetlg,
(select sum(nota_detail.intquantity*nota_detail.intharga)
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week in (select intid_week from week where intbulan = '$month')
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = $jpenjualan
and nota_detail.is_free = 0
and barang.intid_jbarang=6
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_dealer = a.intid_dealer) as omsetll
		from nota a, nota_detail b, barang c
		where a.intid_nota = b.intid_nota
		and b.intid_barang = c.intid_barang
		and a.intid_week in (select intid_week from week where intbulan = '$month')
		and a.intid_cabang = $cabang
		and a.intid_jpenjualan = $jpenjualan
		and b.is_free = 0
		and a.is_arisan = 0
		and a.is_dp = 0
		group by a.intid_dealer)y
		where x.intid_dealer = y.intid_dealer");
        return $query->result();
	}	
	function get_CetakPenjualanSKBulanan($month, $cabang)
	{
			$query = $this->db->query("select a.datetgl, a.intno_nota, upper(b.strnama_dealer) strnama_dealer, upper(b.strnama_upline) strnama_upline, 
		upper(d.strnama_unit)strnama_unit,upper(c.strnama_cabang) strnama_cabang, a.intid_week,  
		f.strnama, e.intquantity, h.intharga_jawa, h.intharga_luarjawa, c.intid_wilayah, sum(a.inttotal_bayar)inttotal_bayar 
		from nota a inner join cabang c on c.intid_cabang = a.intid_cabang
		inner join member b on b.intid_dealer = a.intid_dealer
		inner join unit d on d.intid_unit = b.intid_unit,
		nota_detail e, barang f, jenis_barang g, harga h
		where a.intid_nota = e.intid_nota
		and e.intid_barang = f.intid_barang
		and f.intid_jbarang = g.intid_jbarang
		and f.intid_barang = h.intid_barang
		and a.intid_week in (select intid_week from week where intbulan = '$month')
		and a.intid_cabang = $cabang
		and a.intid_jpenjualan = 10
		and e.is_free = 0
		group by a.datetgl, a.intno_nota, b.strnama_dealer, b.strnama_upline, d.strnama_unit,
		d.strnama_unit, a.intid_week, f.strnama, e.intquantity, h.intharga_jawa, h.intharga_luarjawa, c.intid_wilayah
		order by d.strnama_unit asc, b.strnama_upline asc");
        return $query->result();
	}
	
	function get_CetakPenjualanArisanBulanan($month, $cabang)
	{
		$query = $this->db->query("select a.datetgl, a.intno_nota, upper(b.strnama_dealer) strnama_dealer, upper(b.strnama_upline) strnama_upline, 
		upper(d.strnama_unit)strnama_unit,upper(c.strnama_cabang) strnama_cabang, a.intid_week,  
		c.intid_wilayah, h.intcicilan_jawa, h.intcicilan_luarjawa, 
		h.intum_jawa, h.intum_luarjawa, h.intharga_jawa, 
		(select sum(inttotal_omset) from nota where nota.intid_week in (select intid_week from week where intbulan = '$month') and nota.intid_cabang = $cabang and nota.intid_jpenjualan = 1 and nota.is_arisan = 1 and nota.intid_nota = a.intid_nota) as inttotal_omset, 
		a.intid_jpenjualan, (select sum(intpv) from nota where nota.intid_week in (select intid_week from week where intbulan = '$month') and nota.intid_cabang = $cabang and nota.intid_jpenjualan = 1 and nota.is_arisan = 1 and nota.intid_nota = a.intid_nota)as intpv, 
		sum(e.intquantity)intquantity, (select sum(inttotal_bayar) from nota where nota.intid_week in (select intid_week from week where intbulan = '$month') and nota.intid_cabang = $cabang and nota.intid_jpenjualan = 1 and nota.is_arisan = 1 and nota.intid_nota = a.intid_nota) inttotal_bayar,	
		(h.intcicilan_jawa*(sum(e.intquantity))*1) as cicilan, 
		(select sum(intkomisi10) from nota where nota.intid_week in (select intid_week from week where intbulan = '$month') and nota.intid_cabang = $cabang and nota.intid_jpenjualan = 1 and nota.is_arisan = 1 and nota.intid_nota = a.intid_nota) as intkomisi10, 
		(select sum(intkomisi20) from nota where nota.intid_week in (select intid_week from week where intbulan = '$month') and nota.intid_cabang = $cabang and nota.intid_jpenjualan = 1 and nota.is_arisan = 1 and nota.intid_nota = a.intid_nota) as intkomisi20,
		(select sum(nota_detail.intharga*nota_detail.intquantity) from nota_detail where nota_detail.intid_nota = a.intid_nota) retail
		from nota a, member b, cabang c, unit d, nota_detail e, 
		barang f, harga h
		where a.intid_jpenjualan = 1
		and a.is_arisan = 1
		and e.is_free = 0
		and a.intid_week in (select intid_week from week where intbulan = '$month')
		and a.intid_dealer = b.intid_dealer
		and a.intid_cabang = $cabang
		and a.intid_nota = e.intid_nota
		and a.intid_cabang = c.intid_cabang
		and a.intid_unit = d.intid_unit
		and e.intid_barang = f.intid_barang
		and f.intid_barang = h.intid_barang
		group by a.intid_nota
		order by d.strnama_unit asc, b.strnama_upline asc");
        return $query->result();
	}
	//Laporan Keuangan Harian
	function get_CetakKeuanganHarianReguler($cabang, $tgl)
	{
		/*$query = $this->db->query("select DISTINCT(a.intno_nota),
				CASE WHEN c.intid_jbarang = 1 THEN a.inttotal_omset ELSE 0 END AS omsettulip,
				CASE WHEN c.intid_jbarang = 2 THEN a.inttotal_omset ELSE 0 END AS omsetmetal,
				CASE WHEN c.intid_jbarang = 3 THEN a.inttotal_omset ELSE 0 END AS omsettc, 
				a.datetgl tgl
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.datetgl = '$tgl'
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 1 
				and b.is_free = 0
				and a.is_dp = 0");*/
		$query = $this->db->query("select a.intno_nota, a.intid_dealer, 
				(case when c.intid_jbarang = 1 then sum(b.intquantity*b.intharga) end) as omsettulip,
				(case when c.intid_jbarang = 2 then sum(b.intquantity*b.intharga) end) as omsetmetal,
				(case when c.intid_jbarang = 3 then sum(b.intquantity*b.intharga) end) as omsettc,
				a.datetgl tgl
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.datetgl = '$tgl'
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 1
				and b.is_free = 0
				and a.is_dp = 0
				group by a.intid_dealer");
        
		return $query->result();
				
	}
	
	function get_CetakKeuanganHarianHut($cabang, $tgl)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				CASE WHEN c.intid_jbarang = 1 THEN a.inttotal_omset ELSE 0 END AS omsettulip,
				CASE WHEN c.intid_jbarang = 2 THEN a.inttotal_omset ELSE 0 END AS omsetmetal,
				CASE WHEN c.intid_jbarang = 3 THEN a.inttotal_omset ELSE 0 END AS omsettc
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.datetgl = '$tgl'
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 2
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
				 
	}
	
	function get_CetakKeuanganHarianChallenge($cabang, $tgl)
	{
		/*$query = $this->db->query("select DISTINCT(a.intno_nota),
				CASE WHEN c.intid_jbarang = 1 THEN a.inttotal_omset ELSE 0 END AS omsettulip,
				CASE WHEN c.intid_jbarang = 2 THEN a.inttotal_omset ELSE 0 END AS omsetmetal,
				CASE WHEN c.intid_jbarang = 3 THEN a.inttotal_omset ELSE 0 END AS omsettc
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.datetgl = '$tgl'
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 3
				and b.is_free = 0
				and a.is_dp = 0");*/
        $query = $this->db->query("select a.intid_dealer, 
		(case when c.intid_jbarang = 1 then ((sum(b.intquantity*b.intharga))-a.intvoucher) end) as omsettulip,
		(case when c.intid_jbarang = 2 then ((sum(b.intquantity*b.intharga))-a.intvoucher) end) as omsetmetal,
		(case when c.intid_jbarang = 3 then ((sum(b.intquantity*b.intharga))-a.intvoucher) end) as omsettc
		from nota a, nota_detail b, barang c
		where a.intid_nota = b.intid_nota
		and b.intid_barang = c.intid_barang
		and a.datetgl = '$tgl'
		and a.intid_cabang = $cabang
		and a.intid_jpenjualan = 3
		and b.is_free = 0
		group by a.intid_dealer");
		return $query->result();
	}
	
	function get_CetakKeuanganHarianTrade($cabang, $tgl)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				CASE WHEN a.inttrade_in = 30 THEN a.inttotal_omset ELSE 0 END AS omsett30,
				CASE WHEN a.inttrade_in = 50 THEN a.inttotal_omset ELSE 0 END AS omsett50
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.datetgl = '$tgl'
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 4
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganHarianFreeHut($cabang, $tgl)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				CASE WHEN c.intid_jbarang = 1 THEN a.inttotal_omset ELSE 0 END AS omsettulip,
				CASE WHEN c.intid_jbarang = 2 THEN a.inttotal_omset ELSE 0 END AS omsetmetal,
				CASE WHEN c.intid_jbarang = 3 THEN a.inttotal_omset ELSE 0 END AS omsettc
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.datetgl = '$tgl'
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 5
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganHarianFree($cabang, $tgl)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				CASE WHEN c.intid_jbarang = 1 THEN a.inttotal_omset ELSE 0 END AS omsettulip,
				CASE WHEN c.intid_jbarang = 2 THEN a.inttotal_omset ELSE 0 END AS omsetmetal,
				CASE WHEN c.intid_jbarang = 3 THEN a.inttotal_omset ELSE 0 END AS omsettc
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.datetgl = '$tgl'
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 6
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganHarianNetto($cabang, $tgl)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				CASE WHEN c.intid_jbarang = 1 THEN a.inttotal_omset ELSE 0 END AS omsettulip,
				CASE WHEN c.intid_jbarang = 2 THEN a.inttotal_omset ELSE 0 END AS omsetmetal,
				CASE WHEN c.intid_jbarang = 3 THEN a.inttotal_omset ELSE 0 END AS omsettc
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.datetgl = '$tgl'
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 7
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganHarianLain($cabang, $tgl)
	{
		$query = $this->db->query("select(select sum(inttotal_bayar) from nota where intid_jpenjualan=10 and datetgl='$tgl' and intid_cabang= $cabang) omsetsk,
						SUM(CASE WHEN c.intid_jbarang = 5 THEN a.inttotal_bayar ELSE 0 END) AS omsetlg,
						SUM(CASE WHEN c.intid_jbarang = 6 THEN a.inttotal_bayar ELSE 0 END) AS omsetll
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.datetgl = '$tgl'
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 8
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganHarianArisan($cabang, $tgl)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				CASE WHEN c.intid_jbarang = 1 THEN a.inttotal_omset ELSE 0 END AS omsettulip,
				CASE WHEN c.intid_jbarang = 2 THEN a.inttotal_omset ELSE 0 END AS omsetmetal, 
				d.intjeniscicilan, d.winner
				from nota a, nota_detail b, barang c, arisan d
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_nota = d.intid_arisan_detail
				and a.datetgl = '$tgl'
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 9
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
	
	
	function get_CetakKeuanganHarianKomisi($cabang, $tgl)
	{
		$query = $this->db->query("select SUM(distinct(a.intkomisi10)) AS komisi10, SUM(distinct(a.intkomisi20)) AS komisi20
						from nota a, nota_detail b, barang c, jenis_barang d, jenis_penjualan e
						where a.intid_nota = b.intid_nota
						and a.intid_jpenjualan = e.intid_jpenjualan
						and b.intid_barang = c.intid_barang
						and c.intid_jbarang = d.intid_jbarang
						and a.is_dp = 0
						and a.datetgl = '$tgl'
						and a.intid_cabang = $cabang
						and a.intid_jpenjualan <>9");
        return $query->result();
	}
	
	function get_CetakKeuanganHarianKomisiArisan($cabang, $tgl)
	{
		$query = $this->db->query("select SUM(distinct(a.intkomisi10)) AS komisi10, SUM(distinct(a.intkomisi20)) AS komisi20
						from nota a, nota_detail b, barang c, jenis_barang d, jenis_penjualan e
						where a.intid_nota = b.intid_nota
						and a.intid_jpenjualan = e.intid_jpenjualan
						and b.intid_barang = c.intid_barang
						and c.intid_jbarang = d.intid_jbarang
						and a.is_dp = 0
						and a.datetgl = '$tgl'
						and a.intid_cabang = $cabang
						and a.intid_jpenjualan =9");
        return $query->result();
	}
	
	function get_CetakKeuanganHarianDP($cabang, $tgl)
	{
		$query = $this->db->query("select sum(intdp) intcash
					from nota a
					where a.is_dp = 1
					and a.datetgl = '$tgl'
					and a.intid_cabang = $cabang");
        return $query->result();
	}
	//End Laporan Keuangan Harian

	function insertpembayaran($data){
		        
		$tgl = $this->input->post('tanggal');
		$i = $this->db->query("select intid_week from week where '$tgl' between dateweek_start and dateweek_end");
        $week = $i->result();
		$data = array(
            'intid_cabang' => $this->input->post('id_cabang'),
            'nama_bank' => $this->input->post('nama_bank'),
			'datetanggal' => $this->input->post('tanggal'),
			'nominal_bayar' => $this->input->post('nominal_bayar'),
			'intid_week' => $week[0]->intid_week,
			'keterangan' => $this->input->post('keterangan')
            
        );
        $this->db->insert('pembayaran', $data);
		        
	}
	
	function selectPembayaran($cabang)
	{
		$query = $this->db->query("select * from pembayaran where intid_cabang = $cabang");
        return $query->result();
	}
	
	function get_Week($month){
		
		$query = $this->db->query("select intbulan, intid_week, date_format(dateweek_start, '%d %M %Y') AS dateweek_start, date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intbulan = $month");
        return $query->result();
	}
	
	//Laporan Keuangan Mingguan
	function get_DateWeek($week){
		
		$query = $this->db->query("select intid_week, date_format(dateweek_start, '%d %M %Y') AS dateweek_start, date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week");
        return $query->result();
	}
	function get_CetakKeuanganMingguanReguler($cabang, $week)
	{
		
		$query = $this->db->query("SELECT * FROM (select DISTINCT(a.intno_nota), a.is_arisan,
			(select if((SELECT co FROM (SELECT intid_dealer,COUNT(*) AS co FROM (SELECT DISTINCT intid_dealer, intid_jbarang FROM nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week = $week
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = 1
and nota_detail.is_free = 0
and nota.is_arisan = 0
and nota.is_dp = 0) AS z GROUP BY intid_dealer) AS xx WHERE xx.intid_dealer = a.intid_dealer) = 2 ,sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga))),sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
from nota
where intid_week = $week
and intid_cabang = $cabang
and intid_jpenjualan = 1
and is_arisan = 0 and nota.intid_dealer = a.intid_dealer)) 
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week = $week
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = 1
and nota_detail.is_free = 0
and barang.intid_jbarang=1
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_dealer = a.intid_dealer) as omsettulip,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 1
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 1
			and nota_detail.is_free = 0
			and barang.intid_jbarang=2
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsetmetal,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 1
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 1
			and nota_detail.is_free = 0
			and barang.intid_jbarang=3
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsettc
			from nota a, nota_detail b, barang c
			where a.intid_nota = b.intid_nota
			and b.intid_barang = c.intid_barang
			and a.intid_week = $week
			and a.intid_cabang = $cabang
			and a.intid_jpenjualan = 1
			and b.is_free = 0
			and a.is_dp = 0) AS z WHERE z.is_arisan = 0");
        
		return $query->result();
		 
		
	}
	
	function get_CetakKeuanganMingguanHut($cabang, $week)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 2
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 2
			and nota_detail.is_free = 0
			and barang.intid_jbarang=1
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsettulip,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 2
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 2
			and nota_detail.is_free = 0
			and barang.intid_jbarang=2
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsetmetal,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 2
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 2
			and nota_detail.is_free = 0
			and barang.intid_jbarang=3
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsettc
			from nota a, nota_detail b, barang c
			where a.intid_nota = b.intid_nota
			and b.intid_barang = c.intid_barang
			and a.intid_week = $week
			and a.intid_cabang = $cabang
			and a.intid_jpenjualan = 2
			and b.is_free = 0
			and a.is_dp = 0");
        
		return $query->result();
		 
	}
	
	function get_CetakKeuanganMingguanChallenge($cabang, $week)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 3
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 3
			and nota_detail.is_free = 0
			and barang.intid_jbarang=1
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsettulip,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 3
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 3
			and nota_detail.is_free = 0
			and barang.intid_jbarang=2
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsetmetal,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 3
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 3
			and nota_detail.is_free = 0
			and barang.intid_jbarang=3
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsettc
			from nota a, nota_detail b, barang c
			where a.intid_nota = b.intid_nota
			and b.intid_barang = c.intid_barang
			and a.intid_week = $week
			and a.intid_cabang = $cabang
			and a.intid_jpenjualan = 3
			and b.is_free = 0
			and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganMingguanTrade($cabang, $week)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				CASE WHEN a.inttrade_in = 30 THEN a.inttotal_omset ELSE 0 END AS omsett30,
				CASE WHEN a.inttrade_in = 50 THEN a.inttotal_omset ELSE 0 END AS omsett50
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week = $week
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 4
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganMingguanFreeHut($cabang, $week)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 5
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 5
			and nota_detail.is_free = 0
			and barang.intid_jbarang=1
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsettulip,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 5
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 5
			and nota_detail.is_free = 0
			and barang.intid_jbarang=2
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsetmetal,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 5
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 5
			and nota_detail.is_free = 0
			and barang.intid_jbarang=3
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsettc
			from nota a, nota_detail b, barang c
			where a.intid_nota = b.intid_nota
			and b.intid_barang = c.intid_barang
			and a.intid_week = $week
			and a.intid_cabang = $cabang
			and a.intid_jpenjualan = 5
			and b.is_free = 0
			and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganMingguanFree($cabang, $week)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 6
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 6
			and nota_detail.is_free = 0
			and barang.intid_jbarang=1
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsettulip,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 6
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 6
			and nota_detail.is_free = 0
			and barang.intid_jbarang=2
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsetmetal,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 6
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 6
			and nota_detail.is_free = 0
			and barang.intid_jbarang=3
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsettc
			from nota a, nota_detail b, barang c
			where a.intid_nota = b.intid_nota
			and b.intid_barang = c.intid_barang
			and a.intid_week = $week
			and a.intid_cabang = $cabang
			and a.intid_jpenjualan = 6
			and b.is_free = 0
			and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganMingguanNetto($cabang, $week)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 7
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 7
			and nota_detail.is_free = 0
			and barang.intid_jbarang=1
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsettulip,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 7
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 7
			and nota_detail.is_free = 0
			and barang.intid_jbarang=2
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsetmetal,
			(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
			from nota
			where intid_week = $week
			and intid_cabang = $cabang
			and intid_jpenjualan = 7
			and is_arisan = 0
			and intid_nota = a.intid_nota) 
			from nota, nota_detail, barang 
			where nota.intid_nota = nota_detail.intid_nota
			and nota_detail.intid_barang = barang.intid_barang
			and nota.intid_week = $week
			and nota.intid_cabang = $cabang
			and nota.intid_jpenjualan = 7
			and nota_detail.is_free = 0
			and barang.intid_jbarang=3
			and nota.is_arisan = 0
			and nota.is_dp = 0
			and nota.intid_nota = a.intid_nota) as omsettc
			from nota a, nota_detail b, barang c
			where a.intid_nota = b.intid_nota
			and b.intid_barang = c.intid_barang
			and a.intid_week = $week
			and a.intid_cabang = $cabang
			and a.intid_jpenjualan = 7
			and b.is_free = 0
			and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganMingguanLain($cabang, $week)
	{
		$query = $this->db->query("select (CASE WHEN c.intid_jbarang = 5 THEN a.inttotal_bayar  ELSE 0 END) AS omsetlg,
						(CASE WHEN c.intid_jbarang = 6 THEN a.inttotal_bayar ELSE 0 END) AS omsetll
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week = $week
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 8
				and b.is_free = 0
				and a.is_dp = 0
				group by a.intid_nota");
        
		return $query->result();
	}
	function selectTotalDpLunasMingguan($cabang, $week)
	{
		$query = $this->db->query("select sum(intdp) dp from nota where (intid_week = ($week-1) or intid_week = $week) and intid_cabang = $cabang and is_dp = 0");
        	return $query->result();
	}
	
	function get_CetakKeuanganMingguanSK($cabang, $week)
	{
		$query = $this->db->query("select sum(inttotal_bayar) total from nota where intid_jpenjualan=10 and intid_week = $week and intid_cabang= $cabang");
        
		return $query->result();
	}
	
	function get_CetakKeuanganMingguanSpecialPrice($cabang, $week)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota), a.inttotal_bayar
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week = $week
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 11
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
//001
	function get_CetakKeuanganMingguanPoint($cabang, $week)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota), a.inttotal_bayar
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week = $week
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 12
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganMingguanArisan($cabang, $week)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota), a.inttotal_bayar
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week = $week
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 1
				and b.is_free = 0
				and a.is_dp = 0
				and a.is_arisan = 1");
        
		return $query->result();
	}
	
	function get_CetakKeuanganMingguanKomisi($cabang, $week)
	{
		$query = $this->db->query("select sum(nota.intkomisi10) komisi10, sum(nota.intkomisi20) komisi20 
									from nota 
									where nota.is_dp = 0
									and nota.intid_week = $week
									and nota.intid_cabang = $cabang
									and nota.is_arisan = 0 ");
        return $query->result();
	}
	
	function get_CetakKeuanganMingguanKomisiArisan($cabang, $week)
	{
		$query = $this->db->query("select sum(nota.intkomisi10) komisi10, sum(nota.intkomisi20) komisi20, sum(nota.inttotal_omset) omset,  sum(nota.inttotal_bayar) netto
									from nota 
									where nota.is_dp = 0
									and nota.intid_week = $week
									and nota.intid_cabang = $cabang
									and nota.is_arisan = 1
									and nota.intid_jpenjualan = 1");
        return $query->result();
	}
	
	function get_CetakKeuanganMingguanDP($cabang, $week)
	{
		$query = $this->db->query("select sum(intdp) intcash
					from nota a
					where a.is_dp = 1
					and a.intid_week = '$week'
					and a.intid_cabang = $cabang");
        return $query->result();
	}
	
	function selectPembayaranMingguan($cabang, $week)
	{
		$query = $this->db->query("select * from pembayaran where intid_cabang = $cabang and intid_week = $week");
        return $query->result();
	}
	
	function selectTotalPembayaranMingguan($cabang, $week)
	{
		$query = $this->db->query("select a.total_bayar + b.total_dp total_bayar
		from 
		(select ifnull(sum(a.inttotal_bayar), 0) total_bayar from nota a where a.intid_week < $week and a.intid_cabang = $cabang and is_dp = 0 and a.intid_jpenjualan <> 4) a, 
		(select ifnull(sum(intdp), 0) total_dp from nota where intid_week < $week and intid_cabang = $cabang and is_dp = 1 and intid_jpenjualan <> 4) b
		");
        return $query->result();
	}
	
	function selectTotalPembayaranMingguanTrade($cabang, $week)
	{
		$query = $this->db->query("select a.total_bayar + b.total_dp total_bayar
		from 
		(select ifnull(sum(a.inttotal_bayar), 0) total_bayar from nota a where a.intid_week < $week and a.intid_cabang = $cabang and is_dp = 0 and a.intid_jpenjualan = 4) a, 
		(select ifnull(sum(intdp), 0) total_dp from nota where intid_week < $week and intid_cabang = $cabang and is_dp = 1 and intid_jpenjualan = 4) b
		");
        return $query->result();
	}
	
	function selectTotalBayarMingguan($cabang, $week)
	{
		$query = $this->db->query("select sum(nominal_bayar) sudah_bayar from pembayaran where intid_week < $week and intid_cabang = $cabang");
        return $query->result();
	}
	//End Laporan Keuangan Mingguan
	//Laporan Keuangan Bulanan
	function get_CetakKeuanganBulananReguler($cabang, $month)
	{
		$query = $this->db->query("SELECT * FROM (select DISTINCT(a.intno_nota), a.is_arisan,
			(select if((SELECT co FROM (SELECT intid_dealer,COUNT(*) AS co FROM (SELECT DISTINCT intid_dealer, intid_jbarang FROM nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week in (select intid_week from week where intbulan = '$month')
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = 1
and nota_detail.is_free = 0
and nota.is_arisan = 0
and nota.is_dp = 0) AS z GROUP BY intid_dealer) AS xx WHERE xx.intid_dealer = a.intid_dealer) = 2 ,sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga))),sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
from nota
where intid_week in (select intid_week from week where intbulan = '$month')
and intid_cabang = $cabang
and intid_jpenjualan = 1
and is_arisan = 0 and nota.intid_dealer = a.intid_dealer)) 
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week in (select intid_week from week where intbulan = '$month')
and nota.intid_cabang = $cabang
and nota.intid_jpenjualan = 1
and nota_detail.is_free = 0
and barang.intid_jbarang=1
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_dealer = a.intid_dealer) as omsettulip,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 1
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 1
				and nota_detail.is_free = 0
				and barang.intid_jbarang=2
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsetmetal,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 1
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 1
				and nota_detail.is_free = 0
				and barang.intid_jbarang=3
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsettc
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week in (select intid_week from week where intbulan = '$month')
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 1
				and b.is_free = 0
				and a.is_dp = 0
				order by a.intno_nota) AS z WHERE z.is_arisan = 0");
        
		return $query->result(); 
		
	}
	
	function get_CetakKeuanganBulananHut($cabang, $month)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 2
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 2
				and nota_detail.is_free = 0
				and barang.intid_jbarang=1
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsettulip,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 2
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 2
				and nota_detail.is_free = 0
				and barang.intid_jbarang=2
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsetmetal,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 2
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 2
				and nota_detail.is_free = 0
				and barang.intid_jbarang=3
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsettc
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week in (select intid_week from week where intbulan = '$month')
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 2
				and b.is_free = 0
				and a.is_dp = 0
				order by a.intno_nota");
        
		return $query->result();
		 
	}
	
	function get_CetakKeuanganBulananChallenge($cabang, $month)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 3
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 3
				and nota_detail.is_free = 0
				and barang.intid_jbarang=1
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsettulip,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 3
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 3
				and nota_detail.is_free = 0
				and barang.intid_jbarang=2
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsetmetal,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 3
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 3
				and nota_detail.is_free = 0
				and barang.intid_jbarang=3
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsettc
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week in (select intid_week from week where intbulan = '$month')
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 3
				and b.is_free = 0
				and a.is_dp = 0
				order by a.intno_nota");
        
		return $query->result();
	}
	
	function get_CetakKeuanganBulananTrade($cabang, $month)
	{
		
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				CASE WHEN a.inttrade_in = 30 THEN a.inttotal_omset ELSE 0 END AS omsett30,
				CASE WHEN a.inttrade_in = 50 THEN a.inttotal_omset ELSE 0 END AS omsett50
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week in (select intid_week from week where intbulan = '$month')
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 4
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganBulananFreeHut($cabang, $month)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 5
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 5
				and nota_detail.is_free = 0
				and barang.intid_jbarang=1
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsettulip,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 5
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 5
				and nota_detail.is_free = 0
				and barang.intid_jbarang=2
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsetmetal,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 5
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 5
				and nota_detail.is_free = 0
				and barang.intid_jbarang=3
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsettc
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week in (select intid_week from week where intbulan = '$month')
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 5
				and b.is_free = 0
				and a.is_dp = 0
				order by a.intno_nota");
        
		return $query->result();
	}
	
	function get_CetakKeuanganBulananFree($cabang, $month)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 6
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 6
				and nota_detail.is_free = 0
				and barang.intid_jbarang=1
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsettulip,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 6
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 6
				and nota_detail.is_free = 0
				and barang.intid_jbarang=2
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsetmetal,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 6
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 6
				and nota_detail.is_free = 0
				and barang.intid_jbarang=3
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsettc
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week in (select intid_week from week where intbulan = '$month')
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 6
				and b.is_free = 0
				and a.is_dp = 0
				order by a.intno_nota");
        
		return $query->result();
	}
	
	function get_CetakKeuanganBulananNetto($cabang, $month)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota),
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 7
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 7
				and nota_detail.is_free = 0
				and barang.intid_jbarang=1
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsettulip,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 7
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 7
				and nota_detail.is_free = 0
				and barang.intid_jbarang=2
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsetmetal,
				(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
				from nota
				where intid_week in (select intid_week from week where intbulan = '$month')
				and intid_cabang = $cabang
				and intid_jpenjualan = 7
				and is_arisan = 0
				and intid_nota = a.intid_nota)
				from nota, nota_detail, barang 
				where nota.intid_nota = nota_detail.intid_nota
				and nota_detail.intid_barang = barang.intid_barang
				and nota.intid_week in (select intid_week from week where intbulan = '$month')
				and nota.intid_cabang = $cabang
				and nota.intid_jpenjualan = 7
				and nota_detail.is_free = 0
				and barang.intid_jbarang=3
				and nota.is_arisan = 0
				and nota.is_dp = 0
				and nota.intid_nota = a.intid_nota) as omsettc
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week in (select intid_week from week where intbulan = '$month')
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 7
				and b.is_free = 0
				and a.is_dp = 0
				order by a.intno_nota");
        
		return $query->result();
	}
	
	function get_CetakKeuanganBulananLain($cabang, $month)
	{
		$query = $this->db->query("select (CASE WHEN c.intid_jbarang = 5 THEN a.inttotal_bayar  ELSE 0 END) AS omsetlg,
						(CASE WHEN c.intid_jbarang = 6 THEN a.inttotal_bayar ELSE 0 END) AS omsetll
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week in (select intid_week from week where intbulan = '$month')
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 8
				and b.is_free = 0
				and a.is_dp = 0
				group by a.intid_nota");
        
		return $query->result();
	}
	
	function get_CetakKeuanganBulananSK($cabang, $month)
	{
		$query = $this->db->query("select sum(inttotal_bayar) total from nota where intid_jpenjualan=10 and substr(datetgl,6,2) = '$month' and intid_cabang= $cabang");
        
		return $query->result();
	}
	
	function get_CetakKeuanganBulananSpesial($cabang, $month)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota), a.inttotal_bayar 
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week in (select intid_week from week where intbulan = '$month')
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 11
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
//001
	function get_CetakKeuanganBulananPoint($cabang, $month)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota), a.inttotal_bayar 
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week in (select intid_week from week where intbulan = '$month')
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 12
				and b.is_free = 0
				and a.is_dp = 0");
        
		return $query->result();
	}
	
	function get_CetakKeuanganBulananArisan($cabang, $month)
	{
		$query = $this->db->query("select DISTINCT(a.intno_nota), a.inttotal_bayar 
				from nota a, nota_detail b, barang c
				where a.intid_nota = b.intid_nota
				and b.intid_barang = c.intid_barang
				and a.intid_week in (select intid_week from week where intbulan = '$month')
				and a.intid_cabang = $cabang
				and a.intid_jpenjualan = 1
				and b.is_free = 0
				and a.is_dp = 0
				and a.is_arisan = 1");
        
		return $query->result();
	}
		
	function get_CetakKeuanganBulananKomisi($cabang, $month)
	{
		$query = $this->db->query("select sum(nota.intkomisi10) komisi10, sum(nota.intkomisi20) komisi20 
									from nota 
									where nota.is_dp = 0
									and nota.intid_week in (select intid_week from week where intbulan = '$month')
									and nota.intid_cabang = $cabang
									and nota.is_arisan = 0");
        return $query->result();
	}
	
	function get_CetakKeuanganBulananKomisiArisan($cabang, $month)
	{
		$query = $this->db->query("select sum(nota.intkomisi10) komisi10, sum(nota.intkomisi20) komisi20, sum(nota.inttotal_omset) omset,  sum(nota.inttotal_bayar) netto
									from nota 
									where nota.is_dp = 0
									and nota.intid_week in (select intid_week from week where intbulan = '$month')
									and nota.intid_cabang = $cabang
									and nota.is_arisan = 1
									and nota.intid_jpenjualan = 1");
        return $query->result();
	}
	
	function get_CetakKeuanganBulananDP($cabang, $month)
	{
		$query = $this->db->query("select sum(intdp) intcash
					from nota a
					where a.is_dp = 1
					and a.intid_week in (select intid_week from week where intbulan = '$month')
					and a.intid_cabang = $cabang");
        return $query->result();
	}
	
	function selectPembayaranBulanan($cabang, $month)
	{
		$query = $this->db->query("select * from pembayaran where intid_cabang = $cabang and substr(datetanggal,6,2) = '$month'");
        return $query->result();
	}
	
	/*function selectTotalPembayaranBulanan($cabang, $month)
	{
		$query = $this->db->query("select SUM(a.inttotal_bayar) total_bayar from nota a where substr(a.datetgl,6,2) = ($month-1) and a.intid_cabang = $cabang");
        return $query->result();
	}*/
	
	function selectTotalPembayaranBulanan($cabang, $month)
	{
		$query = $this->db->query("select a.total_bayar + b.total_dp total_bayar
		from 
		(select ifnull(sum(a.inttotal_bayar), 0) total_bayar from nota a where a.intid_week in (select intid_week from week where intbulan = ($month-1)) and a.intid_cabang = $cabang and is_dp = 0 and a.intid_jpenjualan <> 4) a, 
		(select ifnull(sum(intdp), 0) total_dp from nota where intid_week in (select intid_week from week where intbulan = ($month-1)) and intid_cabang = $cabang and is_dp = 1 and intid_jpenjualan <> 4) b
		");
        return $query->result();
	}
	
	function selectTotalPembayaranBulananTrade($cabang, $month)
	{
		$query = $this->db->query("select a.total_bayar + b.total_dp total_bayar
		from 
		(select ifnull(sum(a.inttotal_bayar), 0) total_bayar from nota a where a.intid_week in (select intid_week from week where intbulan = ($month-1)) and a.intid_cabang = $cabang and is_dp = 0 and a.intid_jpenjualan = 4) a, 
		(select ifnull(sum(intdp), 0) total_dp from nota where intid_week in (select intid_week from week where intbulan = ($month-1)) and intid_cabang = $cabang and is_dp = 1 and intid_jpenjualan = 4) b
		");
        return $query->result();
	}
	
	function selectTotalBayarBulanan($cabang, $month)
	{
		$query = $this->db->query("select sum(nominal_bayar) sudah_bayar from pembayaran where substr(datetanggal,6,2) = ($month-1) and intid_cabang = $cabang");
        return $query->result();
	}
	//End Laporan Keuangan Bulanan	
	//PV
	
	function pv_tree($unit)
	{
	   $sql = "select M.strkode_dealer,  
				IF (M.intlevel_dealer=1, M.strnama_dealer, '') as Level1,
				IF (M.intlevel_dealer=2, M.strnama_dealer, '') as Level2,
				IF (M.intlevel_dealer=3, M.strnama_dealer, '') as Level3,
				IF (M.intlevel_dealer=4, M.strnama_dealer, '') as Level4,
				IF (M.intlevel_dealer=5, M.strnama_dealer, '') as Level5,
				IF (M.intlevel_dealer=6, M.strnama_dealer, '') as Level6,
				IF (M.intlevel_dealer=7, M.strnama_dealer, '') as Level7,
				IF (M.intlevel_dealer=8, M.strnama_dealer, '') as Level8,
				IF (M.intlevel_dealer=9, M.strnama_dealer, '') as Level9,
				IF (M.intlevel_dealer=10, M.strnama_dealer, '') as Level10,
				IF (M.intlevel_dealer=11, M.strnama_dealer, '') as Level11,
				IF (M.intlevel_dealer=12, M.strnama_dealer, '') as Level12,
				IF (M.intlevel_dealer=13, M.strnama_dealer, '') as Level13,
				IF (M.intlevel_dealer=14, M.strnama_dealer, '') as Level14,
				IF (M.intlevel_dealer=15, M.strnama_dealer, '') as Level15,
				IF (M.intlevel_dealer=16, M.strnama_dealer, '') as Level16,
				IF (M.intlevel_dealer=17, M.strnama_dealer, '') as Level17,
				IF (M.intlevel_dealer=18, M.strnama_dealer, '') as Level18,
				IF (M.intlevel_dealer=19, M.strnama_dealer, '') as Level19,
				IF (M.intlevel_dealer=20, M.strnama_dealer, '') as Level20
				from member M
				where M.intid_unit=$unit";
       $query = $this->db->query($sql);
	}
	function pv_levelDealer($unit)
	{
	   //CREATE HEADER
	   $del ="delete from temp_header";
	   $querydel = $this->db->query($del); 
		
	   $sql = "insert into temp_header(strjudul, intvalue) values ('KODE',0)";
       $query = $this->db->query($sql);
	   
	   $sql1 = "select DISTINCT(intlevel_dealer) level from member where intid_unit=$unit";
       $query1 = $this->db->query($sql1); 
		foreach ($query1->result() as $row) {
			$level = $row->level;
			$sql = "insert into temp_header(strjudul, intvalue) values ('DEALER$level',$level)";
       		$query = $this->db->query($sql);
		}
		
	   $max = "select max(intlevel_dealer) maxlevel from member where intid_unit=$unit";
       $querymax = $this->db->query($max);
	   foreach ($querymax->result() as $row) {
			$maxlevel = $row->maxlevel;
			
		}
	   
	   $sql2 = "insert into temp_header(strjudul, intvalue) values ('JMLPRIBADI',$maxlevel+1)";
       $query2 = $this->db->query($sql2);
	   $sql3 = "insert into temp_header(strjudul, intvalue) values ('JMLUNIT',$maxlevel+2)";
       $query3 = $this->db->query($sql3);
	   $sql4 = "insert into temp_header(strjudul, intvalue) values ('PV UNIT',$maxlevel+3)";
       $query4 = $this->db->query($sql4);
	   
	   //DATA
	   $del1 ="delete from temp_pv";
	   $querydel1 = $this->db->query($del1);
	   
	   $sql5 = "insert into temp_pv(strkode_dealer, strnama_dealer, intlevel_dealer, intparent_leveldealer, strkode_upline, omset, pv)(select a.strkode_dealer, a.strnama_dealer, a.intlevel_dealer,
				a.intparent_leveldealer, a.strkode_upline,
				sum(b.inttotal_omset) omset,sum(b.intpv) pv 
				from member a LEFT JOIN nota b on a.intid_dealer=b.intid_dealer
				where a.intid_unit = $unit
				GROUP BY a.strnama_dealer
				order by a.intlevel_dealer asc)";
       $query5 = $this->db->query($sql5); 
		
   }
   
   function get_Header()
	{
		$query = $this->db->query("select * from temp_header");
        return $query->result();
	}
	
	function get_DataPv()
	{
		$query = $this->db->query("select * from temp_pv");
        return $query->result();
	}
	//BOOM
	function cek_level($menu_id) {
		$this->db->select('count(strkode_upline) as record_count');
		$this->db->where('strkode_upline',$menu_id);
		$this->db->from('member');
		return $this->db->get()->row()->record_count;
	}
    
    function child($strkode_upline, $lvl = 0, $slvl = 0) {
		//print_r($level_parent);
		$br = '';
		$lvl++;
		$sql_p = "select * from member where strkode_upline = '$strkode_upline'";
		//var_dump("select * from member where strkode_upline = 'M01027666'");
		$get_p = $this->db->query($sql_p);
		foreach ($get_p->result() as $r_p):
			$set_child = '';
			$folder = '';
			if ($this->cek_level($r_p->strkode_upline) > 0 ):
				$set_child = ', children: ['.$br;
				$slvl = $r_p->intid_dealer;
				$folder = ', isFolder: true';
			else:
				$slvl = 0;
			endif;
			//echo $slvl;
			$selected = '';
			//print_r($r_p);
			//$sql_u = "select * from prc_sys_user_menu where usr_id = '".$usrid."' and menu_id = '".$r_p->menu_id."'";
			//$get_u = $this->db->query($sql_u);
			//if($get_u->num_rows() > 0):
//				$selected = 'select: true, ';
			//endif;
			$selected = 'select: true, ';
			self::$CHILD .= '{'.$selected.'title: "'.$r_p->strnama_dealer.'",
                key: "'.$r_p->intid_dealer	.'"'.$folder.$set_child;
			$this->child($r_p->strkode_dealer, $lvl, $slvl);
			if ($r_p->intid_dealer == $slvl):
				self::$CHILD .= ']},'.$br;
			else:
				self::$CHILD .= '},'.$br;
			endif;
		endforeach;
	}

	function get_child() {
		return self::$CHILD;
	}

    function display_children() {
        
        $query = $this->db->query("SELECT strnama_dealer , CONCAT_WS('_', level3, level2, level1) as level  FROM (SELECT
			t1.strnama_dealer as strnama_dealer,
			t3.strnama_dealer AS level3,
			t2.strnama_dealer AS level2,
			t1.strnama_dealer AS level1
			FROM 
			member  as t1
			LEFT JOIN
			member  as t2
			on 
			t1.strkode_upline = t2.strkode_dealer
			LEFT JOIN
			member  as t3
			on
			t2.strkode_upline = t3.strkode_dealer
			) as depth_table
			
			WHERE
							t1.strkode_upline = '$strkode_upline'
			ORDER BY
			strkode_dealer");

        return $query->result();
    }
	
	 function getTotalParent($kode) {
        $this->db->select('member.intlevel_dealer');
        $this->db->from('member');
        $this->db->where('member.strkode_dealer', $kode);
        return $this->db->get()->result();
    }
	
	 function cek_bulan_start($bulan) {
        $this->db->select('week.intid_week, week.dateweek_start');
        $this->db->from('week');
        $this->db->where('week.intbulan', $bulan);
        $this->db->order_by('week.intid_week');
        return $this->db->get()->result();
    }
	
	function cek_bulan_end($bulan) {
        $this->db->select('max(week.intid_week) intid_week, max(week.dateweek_end) dateweek_end');
        $this->db->from('week');
        $this->db->where('week.intbulan', $bulan);
        $this->db->order_by('week.intid_week');
        return $this->db->get()->result();
    }
	
    function Cari_tanggal($limit,$offset,$tanggal, $dealer, $cabang)
	{
	  if ($cabang ==1)
	  {
	  		$q = $this->db->query("select distinct(a.intno_nota) intno_nota, upper(b.strnama_dealer) strnama_dealer, b.strnama_upline, upper(d.strnama_unit) strnama_unit, a.datetgl, a.is_asi
									from nota a inner join member b on b.intid_dealer = a.intid_dealer
									inner join unit d on d.intid_unit = b.intid_unit
                                    where (a.datetgl like '$tanggal%' or b.strnama_dealer like '$dealer%') 
									LIMIT $offset,$limit");
	  } else {
	  if (!empty($tanggal)){
	  		$q = $this->db->query("select distinct(a.intno_nota) intno_nota, upper(b.strnama_dealer) strnama_dealer, b.strnama_upline, upper(d.strnama_unit) strnama_unit, a.datetgl, a.is_asi
									from nota a inner join member b on b.intid_dealer = a.intid_dealer
									inner join unit d on d.intid_unit = b.intid_unit
                                    where a.datetgl like '$tanggal%' 
									and a.intid_cabang = $cabang
									LIMIT $offset,$limit");
	  } else  if (!empty($dealer)){
	  		$q = $this->db->query("select distinct(a.intno_nota) intno_nota, upper(b.strnama_dealer) strnama_dealer, b.strnama_upline, upper(d.strnama_unit) strnama_unit, a.datetgl, a.is_asi
									from nota a inner join member b on b.intid_dealer = a.intid_dealer
									inner join unit d on d.intid_unit = b.intid_unit
                                    where b.strnama_dealer like '$dealer%' 
									and a.intid_cabang = $cabang
									LIMIT $offset,$limit");
	  } else {
	  		$q = $this->db->query("select distinct(a.intno_nota) intno_nota, upper(b.strnama_dealer) strnama_dealer, b.strnama_upline, upper(d.strnama_unit) strnama_unit, a.datetgl, a.is_asi
									from nota a inner join member b on b.intid_dealer = a.intid_dealer
									inner join unit d on d.intid_unit = b.intid_unit
                                    where (a.datetgl like '$tanggal%' or b.strnama_dealer like '$dealer%') 
									and a.intid_cabang = $cabang
									LIMIT $offset,$limit");
	  }
	  }
	 
	  return $q;
		
	}

	function tot_hal($tabel,$field,$kata)
	{
	    $q = $this->db->query("select * from $tabel where $field like '%$kata%'");
	    return $q;
	}

	function selectnota($intno_nota){
		$i = $this->db->query("select b.intid_barang,b.intquantity 
			from nota a, nota_detail b 
			where a.intid_nota = b.intid_nota 
			and a.intno_nota = '$intno_nota'");
		return $i->result();
	}
	
	function get_stokpusat($where) {
		if(is_array($where)):
			foreach($where as $key=>$val):
				$this->db->where($key,$val);
			endforeach;
		endif;
		return $this->db->get('stok');
	}
	
	function updateStokCab($where, $data)
	{
		if(is_array($where)):
			foreach($where as $key=>$val):
				$this->db->where($key,$val);
			endforeach;
		endif;
		return $this->db->update('stok',$data);
		
	}
	
	function insertstokhistorycab($data)
	{
		return $this->db->insert('stok_history',$data);
	}
	
	function hapus($intno_nota){
      
	   $i = $this->db->query("select a.intid_nota, a.intid_jpenjualan, a.intid_dealer, a.intid_cabang, b.intquantity, b.intid_barang 
			from nota a, nota_detail b 
			where a.intid_nota = b.intid_nota 
			and a.intno_nota = '$intno_nota'");
       $nota = $i->result();
	   $id_nota = $nota[0]->intid_nota;
	   $jenis_penjualan = $nota[0]->intid_jpenjualan;
	   $dealer = $nota[0]->intid_dealer;
	   $this->db->where('intid_nota', $id_nota);
	   $this->db->delete('nota_detail');
	   $this->db->where('intno_nota', $intno_nota);
       $this->db->delete('nota');
	   if ($jenis_penjualan==10){
	   		$this->db->where('intid_dealer', $dealer);
	   		$this->db->delete('member');
	   }
	}
	
	function get_pemenang_arisan($bln, $thn) {
		$query = $this->db->query("select a.datetgl, a.intno_nota, upper(b.strnama_dealer) strnama_dealer, upper(b.strnama_upline) strnama_upline, 
		upper(d.strnama_unit)strnama_unit,upper(c.strnama_cabang) strnama_cabang, a.intid_week,  
		f.strnama, e.intquantity, h.intharga_jawa, h.intharga_luarjawa, c.intid_wilayah, h.intcicilan_jawa, h.intcicilan_luarjawa, 
		h.intum_jawa, h.intum_luarjawa, i.*, a.inttotal_omset, a.intid_jpenjualan, a.intpv, a.intkomisi10, a.intkomisi20  
		from nota a inner join cabang c on c.intid_cabang = a.intid_cabang
		inner join arisan i on i.intid_arisan_detail = a.intid_nota
		inner join member b on b.intid_dealer = a.intid_dealer
		inner join unit d on d.intid_unit = b.intid_unit,
		nota_detail e, barang f, jenis_barang g, harga h
		where a.intid_nota = e.intid_nota
		and e.intid_barang = f.intid_barang
		and f.intid_jbarang = g.intid_jbarang
		and f.intid_barang = h.intid_barang
		and MONTH(a.datetgl)='$bln' AND YEAR(a.datetgl)='$thn'
		and a.intid_jpenjualan = 1
		and a.is_arisan = 1
		and i.urutan_pemenang <> 0
		and e.is_free = 0
		order by d.strnama_unit asc, b.strnama_upline asc");
        return $query->result();
    }
	
	function countData()
	{
	  	return $this->db->count_all($this->tbl);
  	}
	
	function rekapPV($tglstart, $tglend, $intid_jpenjualan) {
		$query = $this->db->query("select a.strnama_dealer, a.strnama_upline, b.strnama_unit, (select sum(inttotal_omset)
from nota where nota.datetgl BETWEEN '$tglstart' and '$tglend' and intid_jpenjualan = $intid_jpenjualan and nota.intid_unit = b.intid_unit) inttotal_omset,
(select sum(intpv)
from nota where nota.datetgl BETWEEN '$tglstart' and '$tglend' and intid_jpenjualan = $intid_jpenjualan and nota.intid_unit = b.intid_unit) intpv
from member a, unit b
where a.intid_unit = b.intid_unit
and a.intlevel_dealer = 1;");
        return $query->result();
    }
	
	function get_CetakPenjualanKomisi($week, $cabang)
	{
		$query=$this->db->query("select b.intid_dealer,a.intno_nota, a.inttotal_omset, b.strnama_dealer, b.strnama_upline,  a.intkomisi10, a.intkomisi20, 
		a.intpv, a.inttotal_bayar, a.intid_week,e.strnama_jpenjualan,
		(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart,
		(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend,
		c.strnama_unit, d.strnama_cabang,
		(case when g.intid_jbarang = 1 then (f.intquantity*f.intharga) end) as omsett,
		(case when g.intid_jbarang = 2 then (f.intquantity*f.intharga) end) as omsetm,
		(case when g.intid_jbarang = 3 then (f.intquantity*f.intharga) end) as omsettc,
		(case when g.intid_jbarang = 5 then (f.intquantity*f.intharga) end) as omsetlg,
		(case when g.intid_jbarang = 6 then (f.intquantity*f.intharga) end) as omsetll   
		from nota a, member b, unit c, cabang d, jenis_penjualan e, nota_detail f, barang g
		where a.intid_dealer = b.intid_dealer 
		and a.intid_cabang = d.intid_cabang
		and b.intid_unit = c.intid_unit
		and a.intid_jpenjualan = e.intid_jpenjualan
		and a.intid_nota = f.intid_nota
		and f.intid_barang = g.intid_barang
		and (a.nokk <> ''or a.is_asi = 1)
		and a.intid_week = $week
		and a.intid_cabang = $cabang
		order by a.intno_nota asc");
        return $query->result();
	}
	
	function get_CetakSales($week, $cabang)
	{
//001
		$query=$this->db->query("SELECT * FROM (select a.datetgl, a.intno_nota, f.strnama_jpenjualan, c.strnama_dealer, c.strnama_upline, d.strnama_unit, 
a.inttotal_omset, IF(intid_nota = ANY (SELECT nota.intid_nota FROM `nota` INNER JOIN `nota_detail` ON nota.intid_nota = nota_detail.intid_nota WHERE `intid_jpenjualan` = 5 ORDER BY `nota`.`intno_nota` ASC), '0', a.intomset10) AS intomset10, a.intomset20, 

(SELECT SUM(nota_detail.intquantity*nota_detail.intharga) 
FROM nota INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang
WHERE nota.intid_week = $week
AND nota.intid_cabang = $cabang
AND (nota.intid_jpenjualan = 5 OR nota.intid_jpenjualan = 7)
AND nota.intid_nota = a.intid_nota) AS omsetnetto,
(SELECT SUM(nota_detail.intquantity*nota_detail.intharga) 
FROM nota INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang
WHERE nota.intid_week = $week
AND nota.intid_cabang = $cabang
AND nota.intid_jpenjualan = 11
AND nota.intid_nota = a.intid_nota) AS omsetspecialprice,
(SELECT SUM(nota_detail.intquantity*nota_detail.intharga) 
FROM nota INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang
WHERE nota.intid_week = $week
AND nota.intid_cabang = $cabang
AND nota.intid_jpenjualan = 12
AND nota.intid_nota = a.intid_nota) AS omsetpoint,
(SELECT DISTINCT nota.inttotal_bayar 
FROM nota INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang
WHERE nota.intid_week = $week
AND nota.intid_cabang = $cabang
AND nota.intid_jpenjualan = 10
AND nota.intid_nota = a.intid_nota) AS omsetsk,

(select sum(nota_detail.intquantity*nota_detail.intharga) 
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week = $week
and nota.intid_cabang = $cabang
and nota.is_arisan = 0
and nota.is_dp = 0
and barang.intid_jbarang=5
and nota.intid_nota = a.intid_nota) as omsetlg,
(select sum(nota_detail.intquantity*nota_detail.intharga) 
from nota, nota_detail, barang 
where nota.intid_nota = nota_detail.intid_nota
and nota_detail.intid_barang = barang.intid_barang
and nota.intid_week = $week
and nota.intid_cabang = $cabang
and barang.intid_jbarang=6
and nota.is_arisan = 0
and nota.is_dp = 0
and nota.intid_nota = a.intid_nota) as omsetll,
a.inttotal_bayar, a.intpv, a.intid_nota, b.strnama_cabang, a.intid_week, (select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart,
(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend, a.is_arisan, 
(select winner from arisan where intid_arisan_detail= a.intid_nota) winner
from nota a, cabang b, member c, unit d, jenis_penjualan f
where c.intid_unit = d.intid_unit
and a.intid_cabang = b.intid_cabang
and a.intid_dealer =  c.intid_dealer
and a.intid_jpenjualan = f.intid_jpenjualan
and a.intid_cabang = $cabang
and a.intid_week = $week
and a.is_dp = 0 
order by a.intid_jpenjualan asc, a.intno_nota asc) AS z
UNION
SELECT * FROM (
SELECT a.datetgl, a.intno_nota, 'Hadiah' AS strnama_jpenjualan, c.strnama_dealer, c.strnama_upline, d.strnama_unit, 
' ' AS inttotal_omset, ' ' AS intomset10, ' ' AS intomset20, ' ' AS omsetnetto, ' ' AS omsetspecialprice, 
' ' AS omsetpoint, ' ' AS omsetsk, ' ' AS omsetlg, ' ' AS omsetll, ' ' AS omsettotal_bayar, ' ' AS intpv, 
a.intid_nota, b.strnama_cabang, a.intid_week, 
(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart, 
(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend, 
' ' AS is_arisan, ' ' AS winner 
FROM nota_hadiah a INNER JOIN cabang b ON a.intid_cabang = b.intid_cabang INNER JOIN member c ON a.intid_dealer = c.intid_dealer INNER JOIN unit d ON c.intid_unit = d.intid_unit 
WHERE a.intid_cabang = $cabang AND a.intid_week = $week) AS x");
        return $query->result();
	}
	
//001
	function get_DetailCetakSales($nota,$jenis)
	{
		if ($jenis == "Hadiah")
		{
			$query=$this->db->query("SELECT c.strnama, b.intquantity, '0' AS is_arisan, '0' AS winner, '' AS intjeniscicilan, '' AS c5, '' AS c7
FROM nota_hadiah a INNER JOIN nota_detail_hadiah b ON a.intid_nota = b.intid_nota INNER JOIN barang c ON b.intid_barang = c.intid_barang
WHERE a.intid_nota = $nota");
		}
		else {
		$query=$this->db->query("select c.strnama, b.intquantity,  a.is_arisan, d.winner, d.intjeniscicilan, d.c5, d.c7
from nota a left join arisan d on a.intid_nota = d.intid_arisan_detail, 
nota_detail b, barang c
where a.intid_nota = b.intid_nota
and b.intid_barang = c.intid_barang
and a.intid_nota = $nota");
}
        return $query->result();
	}
	
}
?>
